with base as (
   select   
             network_code
            ,if(date(input_time)=dt,hour(input_time),null)                     as input_hour
            ,if(date(send_scantime)=dt,hour(send_scantime),null)               as send_hour
            ,if(date(arrival_scantime)=dt,hour(arrival_scantime),null)         as arrival_hour
            ,if(date(deliver_scantime)=dt,hour(deliver_scantime),null)         as deliver_hour
            ,if(date(sign_scantime)=dt,hour(sign_scantime),null)               as sign_hour
            ,count(if(date(input_time)=dt,hour(input_time),null))              as input_times
            ,count(if(date(send_scantime)=dt,hour(send_scantime),null))        as send_times
            ,count(if(date(arrival_scantime)=dt,hour(arrival_scantime),null))  as arrival_times
            ,count(if(date(deliver_scantime)=dt,hour(deliver_scantime),null))  as deliver_times
            ,count(if(date(sign_scantime)=dt,hour(sign_scantime),null))        as sign_times
            ,grouping__id                                                      as group_id
       from jms_dwd.dwd_all_operation_cnt_detail_dt
        where dt='{{ execution_date | cst_ds }}'
          group by 
             network_code
            ,if(date(input_time)=dt,hour(input_time),null)
            ,if(date(send_scantime)=dt,hour(send_scantime),null)      
            ,if(date(arrival_scantime)=dt,hour(arrival_scantime),null)
            ,if(date(deliver_scantime)=dt,hour(deliver_scantime),null)
            ,if(date(sign_scantime)=dt,hour(sign_scantime),null)      
        grouping sets ((network_code,if(date(input_time)=dt,hour(input_time),null)),
                       (network_code,if(date(send_scantime)=dt,hour(send_scantime),null)),      
                       (network_code,if(date(arrival_scantime)=dt,hour(arrival_scantime),null)),
                       (network_code,if(date(deliver_scantime)=dt,hour(deliver_scantime),null)),
                       (network_code,if(date(sign_scantime)=dt,hour(sign_scantime),null)))
),
network as (
   select 
      name
     ,code
     ,provider_desc
     ,city_desc
     ,area_desc
     ,agent_code
     ,agent_name
     ,franchisee_code
     ,franchisee_name
   from jms_dim.dim_sys_network_detail_dt 
   where dt = '{{ execution_date | cst_ds }}'
),
result as (
      select 
      network_code
     ,sum(if(group_id=15,input_times,0))   as input_times
     ,sum(if(group_id=23,send_times,0))      as send_times
     ,sum(if(group_id=27,arrival_times,0))   as arrival_times
     ,sum(if(group_id=29,deliver_times,0))   as deliver_times
     ,sum(if(group_id=30,sign_times,0))      as sign_times
     ,case when group_id=15  then  input_hour
           when group_id=23  then  send_hour
           when group_id=27  then  arrival_hour           
           when group_id=29  then  deliver_hour           
            else sign_hour end as hour 
   from base 
     where ((group_id=15 and input_hour   is not null) or 
            (group_id=23 and send_hour    is not null) or 
            (group_id=27 and arrival_hour is not null) or 
            (group_id=29 and deliver_hour is not null) or 
            (group_id=30 and sign_hour    is not null))
   group by  network_code
            ,case when group_id=15  then  input_hour
                  when group_id=23  then  send_hour
                  when group_id=27  then  arrival_hour           
                  when group_id=29  then  deliver_hour               
                   else sign_hour end 
)
insert overwrite table jms_dws.dws_all_operation_cnt_hour_sum_dt partition(dt='{{ execution_date | cst_ds }}')
   select 
      network_code
     ,name
     ,input_times
     ,send_times
     ,arrival_times
     ,deliver_times
     ,sign_times
     ,provider_desc
     ,city_desc
     ,area_desc
     ,agent_code
     ,agent_name
     ,franchisee_code
     ,franchisee_name
     ,hour
   from  result
     left join network 
        on network.code=result.network_code
        distribute by 1;